package vashion.azeroth.back.core.excel;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import vashion.azeroth.core.item.pojo.PropertyValueDO;

public class ReadExcel {
	private static final String ITEM_CODE_TITLE = "Code"; 
	private static final String SIZE_TITLE = "Size"; 
	private static final String STOCK_TITLE_1 = "Quantity"; 
	private static final String STOCK_TITLE_2 = "Stock";
	// 总行数
	private int totalRows = 0;
	// 总条数
	private int totalCells = 0;
	// 错误信息接收器
	private String errorMsg;

	// 构造方法
	public ReadExcel() {
	}

	// 获取总行数
	public int getTotalRows() {
		return totalRows;
	}

	// 获取总列数
	public int getTotalCells() {
		return totalCells;
	}

	// 获取错误信息
	public String getErrorInfo() {
		return errorMsg;
	}

	/**
	 * 验证EXCEL文件
	 * 
	 * @param filePath
	 * @return
	 */
	public boolean validateExcel(String filePath) {
		if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))) {
			errorMsg = "文件名不是excel格式";
			return false;
		}
		return true;
	}

	/**
	 * 读EXCEL文件，获取客户信息集合
	 * 
	 * @param fileName
	 * @param mfile
	 * @return
	 * @throws Exception
	 */
	public Map<String, List<PropertyValueDO>> getExcelInfo(String fileName, MultipartFile mfile) throws Exception {

		// 初始化客户信息的集合
		Map<String, List<PropertyValueDO>> skuList = null;
		// 初始化输入流
		InputStream is = null;
		try {
			// 验证文件名是否合格
			if (!validateExcel(fileName)) {
				errorMsg = "请上传*.xls或者*.xlsx文件";
				return null;
			}
			// 根据文件名判断文件是2003版本还是2007版本
			boolean isExcel2003 = true;
			if (WDWUtil.isExcel2007(fileName)) {
				isExcel2003 = false;
			}
			is = mfile.getInputStream();
			// 根据excel里面的内容读取客户信息
			skuList = getExcelInfo(is, isExcel2003);
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (is != null) {
				is.close();
			}
		}
		return skuList;
	}

	/**
	 * 根据excel里面的内容读取客户信息
	 * 
	 * @param is
	 *            输入流
	 * @param isExcel2003
	 *            excel是2003还是2007版本
	 * @return
	 * @throws IOException
	 */
	public Map<String, List<PropertyValueDO>> getExcelInfo(InputStream is, boolean isExcel2003) throws IOException {
		Map<String, List<PropertyValueDO>> skuList = null;

		/** 根据版本选择创建Workbook的方式 */
		Workbook wb = null;
		// 当excel是2003时
		if (isExcel2003) {
			wb = new HSSFWorkbook(is);
		} else {// 当excel是2007时
			wb = new XSSFWorkbook(is);
		}
		// 读取Excel里面客户的信息
		skuList = readExcelValue(wb);

		return skuList;
	}

	/**
	 * 读取Excel里面客户的信息
	 * 
	 * @param wb
	 * @return
	 */
	private Map<String, List<PropertyValueDO>> readExcelValue(Workbook wb) {
		// 得到第一个shell
		Sheet sheet = wb.getSheetAt(0);

		// 得到Excel的行数
		this.totalRows = sheet.getPhysicalNumberOfRows();

		// 得到Excel的列数(前提是有行数)
		if (totalRows >= 1 && sheet.getRow(0) != null) {
			this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
		}

		// 读取和检查标题行
		Row titleRow = sheet.getRow(0);
		int itemCodeIndex = -1, sizeIndex = -1, stockIndex = -1;
		for (int cellIndex = 0; cellIndex < this.totalCells; cellIndex++) {
			Cell cell = titleRow.getCell(cellIndex);
			if (null != cell) {
				String cellValue = cell.getStringCellValue();
				if (StringUtils.isNotEmpty(cellValue)) {
					if (cellValue.toLowerCase().contains(ITEM_CODE_TITLE.toLowerCase())) {
						itemCodeIndex = cellIndex;
					} else if (cellValue.toLowerCase().contains(SIZE_TITLE.toLowerCase())) {
						sizeIndex = cellIndex;
					} else if (cellValue.toLowerCase().contains(STOCK_TITLE_1.toLowerCase())
							|| cellValue.toLowerCase().contains(STOCK_TITLE_2.toLowerCase())) {
						stockIndex = cellIndex;
					}
				}
			}
		}
		// 标题行不规范，返回错误信息
		if (itemCodeIndex < 0) {
			errorMsg = "请将商品编码列标题改为\"" + ITEM_CODE_TITLE + "\"</br> ";
		}
		if (stockIndex < 0) {
			errorMsg += "请将可售数量列标题改为\"" + STOCK_TITLE_1 + "\"或\"" + STOCK_TITLE_2 + "\"。 ";
		}
		if (StringUtils.isNotEmpty(errorMsg)) {
			return null;
		}

		Map<String, List<PropertyValueDO>> skuMap = Maps.<String, List<PropertyValueDO>>newHashMap();
		PropertyValueDO propertyValue;
		// 循环Excel行数,从第二行开始。标题不入库
		for (int r = 1; r < totalRows; r++) {
			Row row = sheet.getRow(r);
			if (row == null)
				continue;
			propertyValue = new PropertyValueDO();
			String itemCode = null;
			// 循环Excel的列
			for (int c = 0; c < this.totalCells; c++) {
				Cell cell = row.getCell(c);
				if (null != cell) {
					if (c == itemCodeIndex) {
						int cellType = cell.getCellType();
						if (Cell.CELL_TYPE_NUMERIC == cellType) {
							itemCode = new Double(cell.getNumericCellValue()).toString();//商品编码列
						} else if (Cell.CELL_TYPE_STRING == cellType) {
							itemCode = cell.getStringCellValue();//商品编码列
						}
					} else if (c == sizeIndex) {
						int cellType = cell.getCellType();
						if (Cell.CELL_TYPE_NUMERIC == cellType) {
							propertyValue.setValue(new Double(cell.getNumericCellValue()).toString());//商品尺寸
						} else if (Cell.CELL_TYPE_STRING == cellType) {
							propertyValue.setValue(cell.getStringCellValue());//商品尺寸
						}
					} else if (c == stockIndex) {
						int cellType = cell.getCellType();
						if (Cell.CELL_TYPE_NUMERIC == cellType) {
							propertyValue.setStock((int)(cell.getNumericCellValue()));//商品可售数量
						} else if (Cell.CELL_TYPE_STRING == cellType) {
							propertyValue.setStock((int)Float.parseFloat(cell.getStringCellValue()));//商品可售数量
						}
					} 
				}
			}
			if (StringUtils.isNotEmpty(itemCode)) {
				// 添加sku
				List<PropertyValueDO> propertyValueDOList = skuMap.get(itemCode);
				if (null == propertyValueDOList) {
					propertyValueDOList = Lists.<PropertyValueDO>newArrayList();
					skuMap.put(itemCode, propertyValueDOList);
				}
				propertyValueDOList.add(propertyValue);
			}
		}
		return skuMap;
	}
}
